// sqlite3db.d : The D wrapper for SQLite3 Database
// Author: Wei Li (oldrev@gmail.com)
// License: GPL

module dayudict.sqlite.sqlite3db;

import std.string;
import std.typetuple;

import dayudict.sqlite.sqlite3_c;
import dayudict.util.variant;

alias Variant!(int, double, char[], ubyte[]) Sqlite3Variant;

class DBException : Exception
{
	private int m_err;

	public this(char[] msg, int err)
	{
		m_err = err;
		super(msg);
	}

	public int errno()
	{
		return m_err;
	}
}


class Sqlite3Connection
{
	invariant
	{
		assert(m_sqlite3 !is null);
	}

	private sqlite3* m_sqlite3 = null;

	/**
	  打开 SQLite3 数据库
	*/
	public this(char[] name)
	in
	{
		assert(name !is null);
		assert(name.length > 0);
	}
	body
	{
		int errno = sqlite3_open(toStringz(name), &m_sqlite3);
		if(errno != SQLITE_OK) 
			throw new DBException("Failed to open or create " ~ name, errno);
	}

	public ~this()
	{
		close();
	}

	public void close()
	{
		//忽略返回值，就所有问题也认为正确关闭了数据库，切记不要在 dtor 里扔异常
		sqlite3_close(m_sqlite3);
	}

	/**
	  执行数据操作类的 SQL 语句
	*/
	public void executeDML(char[] sql) 
	in
	{
		assert(sql !is null);
		assert(sql.length > 0);
	}
	body
	{
		int errno = sqlite3_exec(m_sqlite3, toStringz(sql), null, null, null);
		if(errno != SQLITE_OK)
			throw new DBException("Failed to execute: " ~ sql, errno);
	}

	/** 
	  执行查询类 SQL 语句
	*/
	public Sqlite3DataReader query(char[] sql)
	in
	{
		assert(sql !is null);
		assert(sql.length > 0);
	}
	out(r)
	{
		assert(r !is null);
	}
	body
	{
		sqlite3_stmt* stmt = null;
		int errno = 
			sqlite3_prepare(m_sqlite3, toStringz(sql), sql.length, &stmt, null);
		if(errno != SQLITE_OK)
			throw new DBException("Failed to query: " ~ sql, errno);
		return new Sqlite3DataReader(stmt);
	}

}

/**
 *	表示 SQLite3 数据库的查询结果集合
 */
class Sqlite3DataReader
{
	invariant
	{
		assert(m_stmt !is null);
	}

	private sqlite3_stmt* m_stmt = null;

	/**
	  * ctor
	  */
	public this (sqlite3_stmt* stmt) 
	in
	{
		assert(stmt !is null);
	}
	body
	{		
		m_stmt = stmt;
	}

	public ~this()
	{
		close();
	}

	/**
	  * 列数
	  */
	public int fieldCount()
	out(r)
	{
		assert(r >= 0);
	}
	body
	{
		return sqlite3_column_count(m_stmt);
	}

	/**
	  * 返回命名字段的索引号
	  * 不区分大小写
	  */
	public int getOrdinal(char[] name)
	in
	{
		assert(name !is null);
		assert(name.length > 0);
	}
	out(r)
	{
		assert(r >= 0 && r < fieldCount);	
	}
	body
	{
		for(int i = 0; i < fieldCount; i++)
		{
			char* p = sqlite3_column_name(m_stmt, i);
			if(icmp(name, std.string.toString(p)) == 0)
			{
				return i;
			}
		}
		
		return -1; //not found
	}

	/**
	  * 返回指定列的名称
	  */
	public char[] getName(int col)
	in
	{
		assert(col >= 0 && col < fieldCount);	
	}
	out(r)
	{
		assert(r !is null);
		assert(r.length > 0);
	}
	body
	{
		return std.string.toString(sqlite3_column_name(m_stmt, col)).dup;
	}


	/**
	  * 返回指定列的字符串值
	  */
	public char[] getString(int col)
	in
	{
		assert(col >= 0 && col < fieldCount);	
	}
	out(r)
	{
		assert(r !is null);
		assert(r.length > 0);
	}
	body
	{
		int type = sqlite3_column_type(m_stmt, col);
		if(type != SQLITE_TEXT)
			throw new DBException("Invalid Type", 0);
		char* t = cast(char*)sqlite3_column_text(m_stmt, col);
		return std.string.toString(t).dup;
	}


	/**
	  * 返回指定列的 blob 值 
	  */
	public ubyte[] getBytes(int col)
	in
	{
		assert(col >= 0 && col < fieldCount);	
	}
	out(r)
	{
		assert(r !is null);
		assert(r.length > 0);
	}
	body
	{
		int type = sqlite3_column_type(m_stmt, col);
		if(type != SQLITE_BLOB)
			throw new DBException("Invalid Type", 0);
		ubyte* p = cast(ubyte*)sqlite3_column_blob(m_stmt, col);
		int len = sqlite3_column_bytes(m_stmt, col);
		return p[0 .. len].dup;
	}



	/**
	  * 返回指定列的值
	  */
	public Sqlite3Variant getValue(int col)
	in
	{
		assert(col >= 0 && col < fieldCount);	
	}
	out(r)
	{
		assert(r.which >= 0);
	}
	body
	{
		int type = sqlite3_column_type(m_stmt, col);
		Sqlite3Variant var;	

		switch(type)
		{
			case SQLITE_NULL:
				assert(false, "SQLITE_NULL type is not supported");
				break;

			case SQLITE_BLOB:
				var = getBytes(col);
				break;

			case SQLITE_INTEGER:
				var = sqlite3_column_int(m_stmt, col);
				break;

			case SQLITE_FLOAT:
				var = sqlite3_column_double(m_stmt, col);
				break;

			case SQLITE_TEXT:
				var = getString(col);
				break;


			default:
			  throw new DBException("Bad column type", 0);
			  break;
		}
	}

	/**
	  * 返回命名列的值
	  */
	Sqlite3Variant getValue(char[] name)
	{
		return getValue( getOrdinal(name) );
	}
	
	
	/**
	  * [] overloading
	  */
	public Sqlite3Variant opIndex(int index)
	{
		return getValue(index);
	}

	
	/**
	  * [] overloading
	  */
	public Sqlite3Variant opIndex(char[] name)
	{
		return getValue(name);
	}


	/**
	  * 前进到下一行
	  * Returns:
	  * true: 还有剩余的行
	  * false: 无剩余行
	  */
	public bool read()
	{		
		return sqlite3_step(m_stmt) == SQLITE_ROW;
	}


	/**
	 * 关闭 SQLite3 的资源
	 */
	public void close() 
	{
		if (m_stmt !is null) 
		{
			sqlite3_finalize(m_stmt);
			m_stmt = null;
		}
	}
}
